﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class RequestControl_BLL : IProjectControl
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProject(int Year, string HeaderID, string StrategySeq, string SourceID)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataStrategyControl(int Year, string JobSiteID, bool groupbyproject, string RecordStatus)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@jobsite", JobSiteID);
                dbManager.AddParameters(1, "@year", Year);
                dbManager.AddParameters(2, "@RecordStatus", RecordStatus);
                dbManager.AddParameters(3, "@DeleteRecordStatus", SystemInfo._DeleteRecordStatus);

                string sql = "select d.bgm_strat_list_seq, d.bgm_strat_list_name as name"
                    + ", d2.bgm_strat_list_seq as act_seq, d2.bgm_strat_list_name as act_name"
                    + ", d3.bgm_strat_list_seq as output_seq, d3.bgm_strat_list_name as output_name"
                    + ", coalesce(sum(e.total_amount), 0) as total_amount"
                    + " from bgm_request a"
                       + " inner join bgm_request_expense e on a.bgm_req_seq = e.bgm_req_seq"

                    + " inner join bgm_strategy_data_mapping m1 on a.bgm_strat_list_seq = m1.bgm_strat_list_seq"
                        + " and m1.bgm_year = @year"
                    + " inner join bgm_strategy_data_mapping m2 on m1.upper_bgm_strat_data_map_seq = m2.bgm_strat_data_map_seq"
                        + " and m2.bgm_year = @year"
                    + " inner join bgm_strategy_data_mapping m3 on m2.upper_bgm_strat_data_map_seq = m3.bgm_strat_data_map_seq"
                        + " and m3.bgm_year = @year"

                       + " inner join bgm_strategy_data_list d on d.bgm_strat_list_seq = a.bgm_strat_list_seq"
                       + " inner join bgm_strategy_data_list d2 on d2.bgm_strat_list_seq = m2.bgm_strat_list_seq"
                       + " inner join bgm_strategy_data_list d3 on d3.bgm_strat_list_seq = m3.bgm_strat_list_seq"
                    + " where 0 = 0"
                        + (RecordStatus.Length > 0 ? (RecordStatus == "P" ? " and a.record_status in ('P', 'A')" : " and a.record_status = @RecordStatus")
                            : " and a.record_status not like @DeleteRecordStatus")
                        + (Year > 0 ? " and a.bgm_year = @year" : "")
                        + (JobSiteID.Length > 0 ? " and (a.job_site_seq = @jobsite"
                            + " or a.job_site_seq_main = @jobsite"
                            + " or exists (select rp.* from bgm_request_pass rp"
                                + " where rp.bgm_req_seq = a.bgm_req_seq"
                                + " and rp.job_site_seq = @jobsite))" : "")
                    + " group by d.bgm_strat_list_seq, d.bgm_strat_list_name"
                        + ", d2.bgm_strat_list_seq, d2.bgm_strat_list_name, d3.bgm_strat_list_seq, d3.bgm_strat_list_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectByOption(int Year, string JobSiteID, string Strategy, string RecordStatus)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@jobsite", JobSiteID);
                dbManager.AddParameters(1, "@year", Year);
                dbManager.AddParameters(2, "@strategy", Strategy);
                dbManager.AddParameters(3, "@RecordStatus", RecordStatus);

                string sql = "select mo.bgm_option_seq, mo.bgm_option_name as name"
                            + ", sum((weight * coalesce(e.total_amount, 0)) / 100) as total_amount"
                            + " from bgm_request r"
                               + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                               + " inner join bgm_request_option o on o.bgm_req_seq = r.bgm_req_seq"
                               + " inner join bgm_mas_option mo on mo.bgm_option_seq = o.bgm_option_seq"

                           + " where r.record_status = @RecordStatus"
                                + ((Year > 0) ? " and r.bgm_year = @year" : "")
                                + (JobSiteID.Length > 0 ? " and (r.job_site_seq = @jobsite"
                                    + " or r.job_site_seq_main = @jobsite"
                                    + " or exists (select rp.* from bgm_request_pass rp"
                                        + " where rp.bgm_req_seq = r.bgm_req_seq"
                                        + " and rp.job_site_seq = @jobsite))" : "")
                                + ((Strategy.Length > 0) ? " and r.bgm_strat_list_seq = @strategy" : "")
                                + " and mo.bgm_option_type_seq = 3"
                            + " group by mo.bgm_option_seq, mo.bgm_option_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequence(string seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequenceForGridIn(string JobSiteSeq, string Strategy)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@jobsite", JobSiteSeq);
                dbManager.AddParameters(1, "@strategy", Strategy);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql = "select s.bgm_source_seq, s.bgm_source_name"
                            + ", coalesce(sum(r.total_amount), 0) as total_amount"
                            + " from  bgm_mas_source s "
                               + " left join (select e.bgm_source_seq, sum(e.total_amount) as total_amount"
                                    + " from bgm_request r"
                                        + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                                    + " where r.job_site_seq = @jobsite"
                                        + ((Strategy.Length > 0) ? " and r.bgm_strat_list_seq = @strategy" : "")
                                    + " group by e.bgm_source_seq"
                                + " ) r on r.bgm_source_seq = s.bgm_source_seq"
                            + " where s.record_status = @RecordStatus"
                               + " and s.bgm_source_seq not in (select upper_bgm_source_seq from bgm_mas_source where record_status = @RecordStatus and upper_bgm_source_seq is not null)"
                            + " group by s.bgm_source_seq, s.bgm_source_name"
                            + " order by s.bgm_source_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequenceForGridIn(string Year, string JobSiteSeq, string Strategy, string RecordStatus)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@jobsite", JobSiteSeq);
                dbManager.AddParameters(1, "@strategy", Strategy);
                dbManager.AddParameters(2, "@year", Year);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@RecordStatus2", RecordStatus);

                string sql = "select s.bgm_source_seq, s.bgm_source_code, s.bgm_source_name"
                            + ", coalesce(sum(r.total_amount), 0) as total_amount"
                            + " from  bgm_mas_source s "
                               + " left join (select e.bgm_source_seq, sum(e.total_amount) as total_amount"
                                    + " from bgm_request r"
                                        + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                                    + " where 0 = 0"
                                    + (RecordStatus.Length > 0 ? (RecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = @RecordStatus2")
                                        : " and r.record_status not like @RecordStatus")
                                    + ((Year.Length > 0) ? " and r.bgm_year = @year" : "")
                                    + (JobSiteSeq.Length > 0 ? " and (r.job_site_seq = @jobsite"
                                        + " or r.job_site_seq_main = @jobsite"
                                        + " or exists (select rp.* from bgm_request_pass rp"
                                            + " where rp.bgm_req_seq = r.bgm_req_seq"
                                            + " and rp.job_site_seq = @jobsite))" : "")
                                    + ((Strategy.Length > 0) ? " and r.bgm_strat_list_seq = @strategy" : "")
                                    + " group by e.bgm_source_seq"
                                + " ) r on r.bgm_source_seq = s.bgm_source_seq"
                            + " where s.record_status not like @RecordStatus"
                               + " and s.bgm_source_seq not in (select upper_bgm_source_seq from bgm_mas_source where record_status not like @RecordStatus and upper_bgm_source_seq is not null)"
                            + " group by s.bgm_source_seq, s.bgm_source_code, s.bgm_source_name"
                            + " order by s.bgm_source_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpGrpBySequenceForGridIn(string Year, string JobSiteSeq, string Strategy, string RecordStatus)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@jobsite", JobSiteSeq);
                dbManager.AddParameters(1, "@strategy", Strategy);
                dbManager.AddParameters(2, "@year", Year);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@RecordStatus2", RecordStatus);

                string sql = "select s.bgm_exp_grp_seq, s.bgm_exp_grp_name"
                            + ", coalesce(sum(r.total_amount), 0) as total_amount"

                            + " from  bgm_mas_expense_group s "
                               + " left join (select exp.bgm_exp_grp_seq, sum(e.total_amount) as total_amount"
                                    + " from bgm_request r"
                                        + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                                        + " inner join bgm_mas_expense exp on e.bgm_exp_seq = exp.bgm_exp_seq"
                                    + " where 0 = 0"
                                    + (RecordStatus.Length > 0 ? (RecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = @RecordStatus2")
                                        : " and r.record_status not like @RecordStatus")
                                    + ((Year.Length > 0) ? " and r.bgm_year = @year" : "")
                                    + (JobSiteSeq.Length > 0 ? " and (r.job_site_seq = @jobsite"
                                        + " or r.job_site_seq_main = @jobsite"
                                        + " or exists (select rp.* from bgm_request_pass rp"
                                            + " where rp.bgm_req_seq = r.bgm_req_seq"
                                            + " and rp.job_site_seq = @jobsite))" : "")
                                    + ((Strategy.Length > 0) ? " and r.bgm_strat_list_seq = @strategy" : "")
                                    + " group by exp.bgm_exp_grp_seq"
                                + " ) r on r.bgm_exp_grp_seq = s.bgm_exp_grp_seq"
                            + " where s.record_status not like @RecordStatus"
                            + " group by s.bgm_exp_grp_seq, s.bgm_exp_grp_name"
                            + " order by s.bgm_exp_grp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectOption(string JobSiteID, string bgm_option_type_seq, string Strategy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@jobsite", JobSiteID);
                dbManager.AddParameters(1, "@type", bgm_option_type_seq);
                dbManager.AddParameters(2, "@strategy", Strategy);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                string sql = "select sum(total_amount) as total"
                            + " from ( "
                                + " select r.job_site_seq, ao.bgm_option_seq, mo.bgm_option_name"
                                + ", null as weight"
                                + ", sum((weight * coalesce(e.total_amount, 0)) / 100) as total_amount"
                                + " from bgm_request r"
                                    + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                                    + " inner join bgm_request_option ao on r.bgm_req_seq = ao.bgm_req_seq"
                                    + " inner join bgm_mas_option mo on mo.bgm_option_seq = ao.bgm_option_seq"
                                + " where 0 = 0"
                                    + (JobSiteID.Length > 0 ? " and (r.job_site_seq = @jobsite"
                                        + " or r.job_site_seq_main = @jobsite"
                                        + " or exists (select rp.* from bgm_request_pass rp"
                                            + " where rp.bgm_req_seq = r.bgm_req_seq"
                                            + " and rp.job_site_seq = @jobsite))" : "")
                                    + ((Strategy.Length > 0) ? " and r.bgm_strat_list_seq = @strategy" : "")
                                    + " and mo.bgm_option_type_seq = @type"
                                    + " and r.record_status not like @RecordStatus"
                                + " group by  r.job_site_seq, ao.bgm_option_seq , mo.bgm_option_name"
                            + " ) t"
                            + " group by job_site_seq";

                DataSet ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                string total = (ds.Tables[0].Rows.Count > 0) ? ds.Tables[0].Rows[0][0].ToString() : "1";

                sql = "select r.job_site_seq, ao.bgm_option_seq, mo.bgm_option_name"
                            + ", (sum((weight * coalesce(e.total_amount, 0)) / 100) * 100) / " + total + " as weight"
                            + ", sum((weight * coalesce(e.total_amount, 0)) / 100) as total_amount"
                            + " from bgm_request r"
                                + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                                + " inner join bgm_request_option ao on r.bgm_req_seq = ao.bgm_req_seq"
                                + " inner join bgm_mas_option mo on mo.bgm_option_seq = ao.bgm_option_seq"
                            + " where 0 = 0"
                                + (JobSiteID.Length > 0 ? " and (r.job_site_seq = @jobsite"
                                    + " or r.job_site_seq_main = @jobsite"
                                    + " or exists (select rp.* from bgm_request_pass rp"
                                        + " where rp.bgm_req_seq = r.bgm_req_seq"
                                        + " and rp.job_site_seq = @jobsite))" : "")
                                + ((Strategy.Length > 0) ? " and r.bgm_strat_list_seq = @strategy" : "")
                                + " and mo.bgm_option_type_seq = @type"
                                + " and r.record_status not like @RecordStatus"
                            + " group by  r.job_site_seq, ao.bgm_option_seq , mo.bgm_option_name";

                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable GetDataProjectOption(string Year, string JobSiteID, string bgm_option_type_seq, string Strategy)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@jobsite", JobSiteID);
                dbManager.AddParameters(1, "@type", bgm_option_type_seq);
                dbManager.AddParameters(2, "@strategy", Strategy);
                dbManager.AddParameters(3, "@year", Year);
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                string sql = "select sum(total_amount) as total"
                            + " from ( "
                                + " select r.job_site_seq, ao.bgm_option_seq, mo.bgm_option_name"
                                + ", null as weight"
                                + ", sum((weight * coalesce(e.total_amount, 0)) / 100) as total_amount"
                                + " from bgm_request r"
                                    + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                                    + " inner join bgm_request_option ao on r.bgm_req_seq = ao.bgm_req_seq"
                                    + " inner join bgm_mas_option mo on mo.bgm_option_seq = ao.bgm_option_seq"
                                + " where r.record_status not like @RecordStatus"
                                    + ((Year.Length > 0) ? " and r.bgm_year = @year" : "")
                                    + " where 0 = 0"
                                    + (JobSiteID.Length > 0 ? " and (r.job_site_seq = @jobsite"
                                        + " or r.job_site_seq_main = @jobsite"
                                        + " or exists (select rp.* from bgm_request_pass rp"
                                            + " where rp.bgm_req_seq = r.bgm_req_seq"
                                            + " and rp.job_site_seq = @jobsite))" : "")
                                    + ((Strategy.Length > 0) ? " and r.bgm_strat_list_seq = @strategy" : "")
                                    + " and mo.bgm_option_type_seq = @type"
                                + " group by  r.job_site_seq, ao.bgm_option_seq , mo.bgm_option_name"
                            + " ) t"
                            + " group by job_site_seq";

                DataSet ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                string total = (ds.Tables[0].Rows.Count > 0) ? ds.Tables[0].Rows[0][0].ToString() : "1";

                sql = "select r.job_site_seq, ao.bgm_option_seq, mo.bgm_option_name"
                            + ", (sum((weight * coalesce(e.total_amount, 0)) / 100) * 100) / " + total + " as weight"
                            + ", sum((weight * coalesce(e.total_amount, 0)) / 100) as total_amount"
                            + " from bgm_request r"
                                + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                                + " inner join bgm_request_option ao on r.bgm_req_seq = ao.bgm_req_seq"
                                + " inner join bgm_mas_option mo on mo.bgm_option_seq = ao.bgm_option_seq"
                            + " where r.record_status not like @RecordStatus"
                                + ((Year.Length > 0) ? " and r.bgm_year = @year" : "")
                                + " where 0 = 0"
                                + (JobSiteID.Length > 0 ? " and (r.job_site_seq = @jobsite"
                                    + " or r.job_site_seq_main = @jobsite"
                                    + " or exists (select rp.* from bgm_request_pass rp"
                                        + " where rp.bgm_req_seq = r.bgm_req_seq"
                                        + " and rp.job_site_seq = @jobsite))" : "")
                                + ((Strategy.Length > 0) ? " and r.bgm_strat_list_seq = @strategy" : "")
                                + " and mo.bgm_option_type_seq = @type"
                            + " group by  r.job_site_seq, ao.bgm_option_seq , mo.bgm_option_name";

                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectOptionBySequence(string seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectKPI(string bgm_app_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectKPIBySequence(string bgm_app_kpi_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlan(string bgm_app_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlanBySequence(string bgm_app_act_plan_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlanTarget(string bgm_app_act_plan_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListPRojectExpense(string bgm_app_ctrl_seq, string source)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectExpense(string bgm_strat_list_seq, string source)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpenseBySequence(string bgm_app_ctrl_exp_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpenseGroup(string bgm_app_ctrl_seq, string bgm_source_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpensePlan(string bgm_app_ctrl_seq, string bgm_source_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpensePlanBySequence(string bgm_app_ctrl_exp_plan_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertProject(string bgm_app_header_seq, string bgm_year, DateTime bgm_app_date, string bgm_strat_list_seq, string job_site_seq, string prj_type_seq, string bgm_app_manager, string bgm_app_scope, string bgm_app_code, string bgm_app_name, string bgm_app_descr, string bgm_app_reason, string bgm_app_object, string bgm_app_plan, string bgm_app_target, string bgm_app_remark, string CreatedBy)
        {
            try
            {
                return 1;
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectCustoms(string bgm_app_seq, string prj_ctrl_seq, string descr)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectOption(string bgm_app_seq, string descr, Nullable<Double> weight, string bgm_option_seq, string created_by)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectKPI(string bgm_app_kpi_name, string bgm_app_kpi_descr, string unit, string bgm_app_kpi_weight, string bgm_app_seq, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type, string target_q1, string target_q2, string target_q3, string target_q4, string mtef_1, string mtef_2, string mtef_3, string created_by)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int InsertProjectActivityPlan(string bgm_app_seq, string descr, int act_month1, int act_month2, int act_month3, int act_month4, int act_month5, int act_month6, int act_month7, int act_month8, int act_month9, int act_month10, int act_month11, int act_month12, string created_by)
        {
            try
            {
                return 1;
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectActivityPlanTarget(string bgm_app_act_plan_seq, string target, string start_month, string end_month, string created_by)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectExpense(string bgm_app_ctrl_seq, string bgm_source_seq, string bgm_exp_seq, string total_amount, string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price, Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string created_by)
        {
        }

        public void InsertProjectExpensePlan(string bgm_app_ctrl_exp_seq, string amount_month1, string amount_month2, string amount_month3, string amount_month4, string amount_month5, string amount_month6, string amount_month7, string amount_month8, string amount_month9, string amount_month10, string amount_month11, string amount_month12, string created_by)
        {
        }

        public void UpdateProject(string project_sequence, string control_seq, string status, string UpdateBy)
        {
        }

        public void UpdateProjectOption(string Sequence, string descr, Nullable<Double> weight, string bgm_option_seq, string UpdateBy)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectKPI(string Sequence, string bgm_app_kpi_name, string bgm_app_kpi_descr, string unit, string bgm_app_kpi_weight, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type, string target_q1, string target_q2, string target_q3, string target_q4, string mtef_1, string mtef_2, string mtef_3, string UpdateBy)
        {
        }

        public void UpdateProjectActivityPlan(string Sequence, string descr, int act_month1, int act_month2, int act_month3, int act_month4, int act_month5, int act_month6, int act_month7, int act_month8, int act_month9, int act_month10, int act_month11, int act_month12, string UpdateBy)
        {
        }

        public void UpdateProjectActivityPlanTarget(string Sequence, string target, string start_month, string end_month, string UpdateBy)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectExpense(string Sequence, string bgm_app_ctrl_exp_seq, string bgm_source_seq, string bgm_exp_seq, string approve_amount, string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price, Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string UpdateBy)
        {
        }

        public void UpdateProjectExpensePlan(string Sequence, string amount_month1, string amount_month2, string amount_month3, string amount_month4, string amount_month5, string amount_month6, string amount_month7, string amount_month8, string amount_month9, string amount_month10, string amount_month11, string amount_month12, string UpdateBy)
        {
        }

        public void UpdateProjectCustoms(string Sequence, string descr)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteProject(int Seq)
        {
            try
            {
               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectOption(string Seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectKPI(string Seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectActivityPlan(string Seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectActivityPlanTarget(string Seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectExpense(string Seq)
        {
        }

        public void InsertProjectManager(string bgm_app_seq, string emp_seq)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteProjectManager(string bgm_app_seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectManager(string bgm_prj_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool chkDubProjectOption(string bgm_app_seq, string bgm_option_seq)
        {
            try
            {
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectControlByJobSite(string job_site_seq, string HeaderID)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectControlBySequenceForGridIn(string seq, string job_site_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataControlChart(string Year, string SubAct, string Jobsite, string Project, string Source, string ExpenseGroup, string SeriesName)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@Year", Year);
                dbManager.AddParameters(2, "@Jobsite", Jobsite);
                dbManager.AddParameters(3, "@SubAct", SubAct);
                dbManager.AddParameters(4, "@Project", Project);
                dbManager.AddParameters(5, "@Source", Source);
                dbManager.AddParameters(6, "@ExpenseGroup", ExpenseGroup);

                string strSqlSelect = "";
                string strSqlJoin = "";
                string strSqlGroupBy = "";
                string strOptionType = "";
                bool bOption = false;

                switch (SeriesName)
                {
                    case "1":
                        strSqlSelect = " r.bgm_strat_list_seq"
                            + ", s.bgm_strat_list_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq";
                        strSqlGroupBy = " group by r.bgm_strat_list_seq"
                            + ", s.bgm_strat_list_name"
                            + " order by name";
                        break;
                    case "2":
                        strSqlSelect = " r.job_site_seq"
                            + ", j.job_site_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq";
                        strSqlGroupBy = " group by r.job_site_seq"
                            + ", j.job_site_name"
                            + " order by name";
                        break;
                    case "3":
                        strSqlSelect = " r.bgm_req_seq"
                            + ", r.bgm_req_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = "";
                        strSqlGroupBy = " group by r.bgm_req_seq"
                            + ", r.bgm_req_name"
                            + " order by name";
                        break;
                    case "4":
                        strSqlSelect = " e.bgm_source_seq"
                            + ", s.bgm_source_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_mas_source s on e.bgm_source_seq = s.bgm_source_seq";
                        strSqlGroupBy = " group by e.bgm_source_seq"
                            + ", s.bgm_source_name";
                        break;
                    case "5":
                        strSqlSelect = " exp.bgm_exp_grp_seq"
                            + ", grp.bgm_exp_grp_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_mas_expense_group grp on exp.bgm_exp_grp_seq = grp.bgm_exp_grp_seq";
                        strSqlGroupBy = " group by exp.bgm_exp_grp_seq"
                            + ", grp.bgm_exp_grp_name";
                        break;
                    case "6":
                        strSqlSelect = " exp2.bgm_exp_seq"
                            + ", exp2.bgm_exp_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_mas_expense exp1 on exp.upper_bgm_exp_seq = exp1.bgm_exp_seq"
                            + " left join bgm_mas_expense exp2 on exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq";
                        strSqlGroupBy = " group by exp2.bgm_exp_seq"
                            + ", exp2.bgm_exp_name";
                        break;
                    case "7":
                        strSqlSelect = " exp1.bgm_exp_seq"
                            + ", exp1.bgm_exp_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_mas_expense exp1 on exp.upper_bgm_exp_seq = exp1.bgm_exp_seq";
                        strSqlGroupBy = " group by exp1.bgm_exp_seq"
                            + ", exp1.bgm_exp_name";
                        break;
                    case "8":
                        strOptionType = "1";
                        bOption = true;
                        break;
                    case "9":
                        strOptionType = "2";
                        bOption = true;
                        break;
                    case "10":
                        strOptionType = "3";
                        bOption = true;
                        break;
                }

                string sql = "";

                if (bOption)
                {
                    sql = "select ro.bgm_option_seq"
                        + ", case when opt.bgm_option_name is null"
	                        + " then 'ไม่ระบุ'"
	                        + " else opt.bgm_option_name"
	                        + " end as name"
                        + ", sum(coalesce(total_amount, 0) * coalesce(ro.weight, 1)) as total_amount"

                        + " from (select r.bgm_req_seq"
                            + ", sum(coalesce(re.total_amount, 0)) as total_amount"
                            + " from bgm_request r"
                            + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                            + " inner join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq"
                            + " where r.record_status not like @RecordStatus"
                            + (Year.Length > 0 ? " and r.bgm_year = @Year" : "")
                            + (Jobsite.Length > 0 ? " and r.job_site_seq = @Jobsite" : "")
                            + (SubAct.Length > 0 ? " and r.bgm_strat_list_seq = @SubAct" : "")
                            + (Project.Length > 0 ? " and r.bgm_req_seq = @Project" : "")
                            + (Source.Length > 0 ? " and re.bgm_source_seq = @Source" : "")
                            + (ExpenseGroup.Length > 0 ? " and exp.bgm_exp_grp_seq = @ExpenseGroup" : "")
                            + " group by r.bgm_req_seq"
                            + ") r"
                        + " left join bgm_request_option ro on r.bgm_req_seq = ro.bgm_req_seq"
                        + " left join bgm_mas_option opt on ro.bgm_option_seq = opt.bgm_option_seq"
                        + " where opt.bgm_option_type_seq = " + strOptionType
                        + " group by ro.bgm_option_seq"
                        + ", opt.bgm_option_name";
                }
                else
                {
                    sql = "select" + strSqlSelect
                        + " from bgm_request r"
                        + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                        + " left join bgm_mas_expense exp on e.bgm_exp_seq = exp.bgm_exp_seq"
                        + strSqlJoin
                        + " where r.record_status not like @RecordStatus"
                        + (Year.Length > 0 ? " and r.bgm_year = @Year" : "")
                        + (Jobsite.Length > 0 ? " and r.job_site_seq = @Jobsite" : "")
                        + (SubAct.Length > 0 ? " and r.bgm_strat_list_seq = @SubAct" : "")
                        + (Project.Length > 0 ? " and r.bgm_req_seq = @Project" : "")
                        + (Source.Length > 0 ? " and e.bgm_source_seq = @Source" : "")
                        + (ExpenseGroup.Length > 0 ? " and exp.bgm_exp_grp_seq = @ExpenseGroup" : "")
                        + strSqlGroupBy;
                }

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListProjectExpenseGrid(string app_header_seq, string source, string exp_grp)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
